package com.bwda.dsrs.filecenter.service.impl;

import com.bwda.dsrs.base.util.AesEncryptPaddingUtil;
import com.bwda.dsrs.base.util.StringUtil;
import com.bwda.dsrs.filecenter.domain.po.dbinfo.DbInfoPo;
import com.bwda.dsrs.filecenter.domain.po.dbscanresult.DbScanResultPo;
import com.bwda.dsrs.filecenter.service.IRemoteService;
import com.mysql.cj.jdbc.result.ResultSetImpl;
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;

import java.sql.*;
import java.util.*;
import java.util.Date;


/**
 *  sqlserver
 * @author yanzz
 *
 */
@Service("SqlServerRemoteServiceImpl")
public class SqlServerRemoteServiceImpl implements IRemoteService {

    @Value("${aes_key}")
    private String aesKey;
    @Value("${mysql_builtIn_user}")
    private String mysqlBuiltInUser;

    private final Logger logger = LoggerFactory.getLogger(SqlServerRemoteServiceImpl.class);

    private final static String DRIVERNAME= "com.microsoft.sqlserver.jdbc.SQLServerDriver";

    /**
     * 获取数据库连接
     * @param
     * @return
     */
    private Connection getConnect(String url, String name, String password) throws Exception{
        Class.forName(DRIVERNAME);
        return DriverManager.getConnection(url,name,password);

    }

    /**
     *
     * @param dbInfoPo db info po
     * @return
     * @throws Exception
     */
    @Override
    public Connection login(DbInfoPo dbInfoPo) throws Exception {
        //验证是否填写了数据库名称

        String serviceName="testDB";
        if(StringUtil.isNotEmpty(dbInfoPo.getNeedScanDb())){
            serviceName=dbInfoPo.getNeedScanDb();
        }
        StringBuffer url = new StringBuffer();
        url.append("jdbc:sqlserver://").append(dbInfoPo.getDbIp());
        url.append(":").append(dbInfoPo.getDbPort());
        url.append(";DatabaseName=").append(serviceName);
        logger.info("ip:{},name:{}",dbInfoPo.getDbIp(),dbInfoPo.getServiceName());

        String userName = dbInfoPo.getDbAccount();
        String pwdDecryp = AesEncryptPaddingUtil.decrypt(dbInfoPo.getDbPassword(),aesKey);

        return getConnect(url.toString(),userName,pwdDecryp);
    }


    /**
     * 获取所有的数据
     * @param connection connection
     * @param tableName  table name
     * @param columnName column name
     * @param rowNum     row num
     * @param sumRowNum  sum row num
     * @return
     * @throws Exception
     */
    @Override
    public List<Map<String, Object>> getData(Connection connection, String tableName, String columnName, Integer rowNum, Integer sumRowNum) throws Exception {

        String queryColumn  = StringUtils.isBlank(columnName) ? "*": columnName;
        String queryRownum  = (rowNum!=null && rowNum>0 && (sumRowNum-rowNum)>0)  ? "top "+rowNum: "";

        String sql = "select " + queryRownum + " "+ queryColumn + " from "+tableName.split("\\.")[1];

        logger.info("getData will run sql = {}",sql);
        List<Map<String,Object>> res = new ArrayList<>();
        Statement stmt = connection.createStatement();
        ResultSet rs=  stmt.executeQuery(sql);
        //获取键名
        ResultSetMetaData md = rs.getMetaData();
        //获取行的数量
        int columnCount = md.getColumnCount();
        while (rs.next()) {
            Map<String,Object> rowData = new HashMap<>();
            for (int i = 1; i <= columnCount; i++) {
                rowData.put(md.getColumnLabel(i).toLowerCase(), rs.getObject(i));
            }
            res.add(rowData);
        }
        rs.close();
        stmt.close();
        return res;
    }

    @Override
    public List<String> getAllDBList(Connection connection, DbInfoPo dbInfoPo) throws Exception {
        return null;
    }

    @Override
    public List<String> getAllTableList(Connection connection, String dbName) throws Exception {
       /* String[] types = { "TABLE" };
        ResultSet rs = connection.getMetaData().getTables(null,null, "%", types);
        String  userName = "";
        while (rs.next()) {
            //库名
            userName  = rs.getString("TABLE_CAT");
            break;

        }*/
        String sql = "select name from "+dbName+"..sysobjects where xtype='U' and id > 0";
        Statement state = connection.createStatement();
        ResultSet rs = state.executeQuery(sql);
        List<String> stringList = new ArrayList<>();
        while(rs.next()){
            String tableName = rs.getString("name").toLowerCase();
            stringList.add(dbName.concat(".").concat(tableName));
        }
        state.close();
        rs.close();

        return stringList;
    }

    @Override
    public List<DbScanResultPo> getTableColumns(Connection connection, String tableName) throws Exception {
        ResultSet rs = connection.getMetaData().getColumns(null,"%", tableName.split("\\.")[1],"%");
        List<DbScanResultPo> dbScanResultPos = new ArrayList<>();
        while (rs.next()) {
            DbScanResultPo dbScanResultPo = new DbScanResultPo();
            dbScanResultPo.setWordIds("");
            dbScanResultPo.setScanNums(0);
            dbScanResultPo.setMatchNums(0);
            dbScanResultPo.setCreateTime(new Date());
            dbScanResultPo.setUpdateTime(new Date());
            dbScanResultPo.setRemark("");
            dbScanResultPo.setDelFlag(0);
            dbScanResultPo.setTableName(tableName.toLowerCase());
            dbScanResultPo.setColumnRemark(StringUtils.isEmpty(rs.getString("REMARKS"))?"":rs.getString("REMARKS").toLowerCase());
            dbScanResultPo.setColumnName(rs.getString("COLUMN_NAME").toLowerCase());
            dbScanResultPo.setColumnType(rs.getString("TYPE_NAME").toLowerCase());
            dbScanResultPos.add(dbScanResultPo);
        }
        rs.close();
        return dbScanResultPos;
    }

    @Override
    public Integer countRowNum(Connection connection, String tableName) throws Exception {
        String sql = "select count(*)  from " + tableName.split("\\.")[1];
        logger.info("countRowNum will run sql = {}", sql);
        List<Map<String, Object>> res = new ArrayList<>();
        Statement stmt = connection.createStatement();
        ResultSet rs = stmt.executeQuery(sql);
        int count = 0;
        while (rs.next()) {
            count = rs.getInt(1);
        }
        rs.close();
        stmt.close();
        return count;
    }

    @Override
    public Integer countTable(Connection connection, String dbName) throws Exception {
        String[] types = { "TABLE" };
        ResultSet rs = connection.getMetaData().getTables(null,null, "%", types);
        int count = ((ResultSetImpl) rs).getRows().size();
        rs.close();
        return count;
    }

    @Override
    public Integer countView(Connection connection) throws Exception {
        String[] types = { "VIEW" };
        ResultSet rs = connection.getMetaData().getTables(null,null, "%", types);
        int count = 0;
        while (rs.next()) {
            count++;
        }
        rs.close();
        return count;
    }

    @Override
    public Integer countFunction(Connection connection) throws Exception {
        ResultSet rs = connection.getMetaData().getFunctions(null,null, "%");
        int count = 0;
        while (rs.next()) {
            count++;
        }
        rs.close();
        return count;
    }

    @Override
    public Integer countSequence(Connection connection) throws Exception {
        return null;
    }

    /**
     * 获取所有的库的信息
     * @param dbInfoPo db info po
     * @return
     * @throws Exception
     */
    @Override
    public List<Object> getDataBases(DbInfoPo dbInfoPo) throws Exception {

        String url = "jdbc:sqlserver://"+dbInfoPo.getDbIp()+":"+dbInfoPo.getDbPort();
        String userName = dbInfoPo.getDbAccount();
        String pwdDecryp = AesEncryptPaddingUtil.decrypt(dbInfoPo.getDbPassword(),aesKey);
        Connection conn = getConnect(url,userName,pwdDecryp);
        DatabaseMetaData meta = conn.getMetaData();
        ResultSet resultSet = meta.getCatalogs();
        List<Object> list = new ArrayList();
        try{
            while(resultSet.next()){
                String db = resultSet.getString("TABLE_CAT");
                list.add(db);
            }
        }finally {
            if(resultSet != null){
                resultSet.close();
            }

            if(conn != null){
                conn.close();
            }

        }
        return list;
    }
}
